Re: Generating subtotal reports direct from SQL - Mailing list pgsql-general

At 1:32 AM +0100 10/10/07, Gregory Stark wrote:
>"Owen Hartnett" <owen@clipboardinc.com> writes:
>
>>  Such that the final table has additional subtotal rows with the
>>aggregate sum
>>  of the amounts.  I'm thinking I can generate two tables and merge
>>them, but is
>>  there an easier way using a fancy Select statement?
>
>Unfortunately the fancy SQL feature you're looking for is ROLLUP which
>Postgres doesn't support.
>
>I think you would have to do
>
>(
>select id, code, amount from foo where code < 10
>union all
>select null, code, sum(amount) from foo where code < 10 group by code
>) order by code, id
>

Perfect.  Just what I was looking for.

Thanks!

-Owen